IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].RptFinancialActivityReport') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].RptFinancialActivityReport
go
-- =======================================================================
-- Procedure Name: dbo.RptFinancialActivityReport
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
-- Create date: 02/26/2008
--
-- Description: Gets Counts of data of SubDeliverables
--
-- Returns:     INT, 0=Success, -1=Error
-- SampleCall:
-- RptFinancialActivityReport '06/01/2009','06/17/2009','All',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'SS',41359 
-- RptFinancialActivityReport '06/01/2009','06/17/2009','All',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Quinn, Steven','SS',41359 
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 02/26/09    RM    Created based on RptSubContractDeliverableReport
-- 03/12/09    RM	 Added Deliverable Status back in as a parameter
-- 03/16/09    RM	 Fixed stored procedure to work with multiple deliverable statuses 
-- 06/09/09    RM 	Changed @SubcontractSpecialist to @SubmittedBy (pass in 'lastname, firstname')
--
-- =======================================================================
CREATE PROCEDURE dbo.RptFinancialActivityReport
     (@StartDate                         DATETIME,  
     @EndDate                           DATETIME,
     @DeliverableStatus			VARCHAR(5000) = NULL,
     @Institution                       VARCHAR(100) = NULL,
     @Department                        VARCHAR(100) = NULL,
     @Unit                              VARCHAR(100) = NULL,
     @FundNumber                        VARCHAR(1000) = NULL,
     @PrincipalInvestigator             VARCHAR(100) = NULL, 
     @OriginatingSponsor                VARCHAR(100) = NULL,
     @Sponsor                           VARCHAR(100) = NULL,    
     @SubmittedBy             		VARCHAR(100) = NULL,
     @GroupBy                           VARCHAR(20) = NULL,
     @DomainUserId                      INT     ) 
                                       
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     SET ANSI_WARNINGS OFF
     
     -- Declare Local Variables
     DECLARE   @Error              INT,
               @RowCount           INT,
               @ReturnCode         INT
               
     
     DECLARE   @Subcontracts TABLE (
                    FolderNumber             VARCHAR(12)    NULL,
                    InfoEdPropNumber         VARCHAR(50)    NULL,
                    Institution              VARCHAR(50)    NULL,
                    Department               VARCHAR(50)    NULL,
                    Unit                     VARCHAR(50)    NULL,
                    ChiefCode                VARCHAR(4)     NULL,
                    SponsorName              VARCHAR(75)    NULL,
                    SponsorTypeName          VARCHAR(50)    NULL,
                    SecondarySponsor         VARCHAR(75)    NULL,
                    ProposalShortTitle       VARCHAR(64)    NULL,
                    PrincipalInvestigator    VARCHAR(64)    NULL,
                    ProjectStartDate         DATETIME       NULL,
                    ProjectEndDate           DATETIME       NULL,
                    InstrumentType           VARCHAR(50)    NULL,
                    SubContractorName        VARCHAR(75)    NULL,
                    BudgetProjectStartDate   DATETIME       NULL,
                    BudgetProjectEndDate     DATETIME       NULL,
                    ProjectAwardedAmount     MONEY          NULL,
                    BudgetStartDate          DATETIME       NULL,
                    BudgetEndDate            DATETIME       NULL,
                    BudgetFundNumber         VARCHAR(20)    NULL,
                    ExpenseAmount            MONEY          NULL,
                    IndirectExpenses         MONEY          NULL,
                    DirectExpenses           MONEY          NULL,
                    CashBalance              MONEY          NULL,
                    ReportId                 BIGINT         NULL,
                    ReportName               VARCHAR(50)    NULL,
                    REPORTCAT                VARCHAR(80)    NULL,
                    REPORTSTAT               VARCHAR(80)    NULL,
                    SubmittedDate            DATETIME       NULL,
                    DueDate                  DATETIME       NULL,
                    StatusDate               DATETIME       NULL,
                    CompletedDate            DATETIME       NULL,
                    DaysFromDueToCompleted   VARCHAR(5)     NULL,
                    SubmittedBy              VARCHAR(50)    NULL,
                    StatusBy                 VARCHAR(50)    NULL,
                    CompletedBy              VARCHAR(50)    NULL,
                    PeriodStartDate          DATETIME       NULL,
                    PeriodEndDate            DATETIME       NULL,
                    DeliverableComments      VARCHAR(2000)  NULL,
                    REPORTCAT_CODE           VARCHAR(50)    NULL,
                    REPORTSTAT_CODE          VARCHAR(50)    NULL,
                    AgreementId              INT            NULL,
                    InstitutionId            INT            NULL,
                    DepartmentId             INT            NULL,
                    UnitId                   INT            NULL,
                    SponsorId                INT            NULL,
                    PrincipalInvestigatorId  INT            NULL,
                    SubcontractsYN           CHAR(1)        NULL,
                    SubContractorId          INT            NULL,
                    SubOutContractorId       INT            NULL )

     -- Let Detail Proc do the work.
     INSERT    @Subcontracts
                    (FolderNumber,
                    InfoEdPropNumber,
                    Institution,
                    Department,
                    Unit,
                    ChiefCode,
                    SponsorName,
                    SponsorTypeName,
                    SecondarySponsor,
                    ProposalShortTitle,
                    PrincipalInvestigator,
                    ProjectStartDate,
                    ProjectEndDate,
                    InstrumentType,
                    SubContractorName,
                    BudgetProjectStartDate,
                    BudgetProjectEndDate,
                    ProjectAwardedAmount,
                    BudgetStartDate,
                    BudgetEndDate,
                    BudgetFundNumber,
                    ExpenseAmount,
                    IndirectExpenses,
                    DirectExpenses,
                    CashBalance,
                    ReportId,
                    ReportName,
                    REPORTCAT,
                    REPORTSTAT,
                    SubmittedDate,
                    DueDate,
                    StatusDate,
                    CompletedDate,
                    DaysFromDueToCompleted,
                    SubmittedBy,
                    StatusBy,
                    CompletedBy,
                    PeriodStartDate,
                    PeriodEndDate,
                    DeliverableComments,
                    REPORTCAT_CODE,
                    REPORTSTAT_CODE,
                    AgreementId,
                    InstitutionId,
                    DepartmentId,
                    UnitId,
                    SponsorId,
                    PrincipalInvestigatorId,
                    SubcontractsYN,
                    SubContractorId,
                    SubOutContractorId)
     EXECUTE @ReturnCode = dbo.RptFinancialActivityDetailReport
                              @StartDate               = @StartDate,
                              @EndDate                 = @EndDate,
                              @DeliverableStatus       = @DeliverableStatus,   -- Get All
                              @Institution             = @Institution,
                              @Department              = @Department,
                              @Unit                    = @Unit,
                              @ChiefCode               = NULL,
                              @FundNumber              = @FundNumber,
                              @PrincipalInvestigator   = @PrincipalInvestigator, 
                              @OriginatingSponsor      = @OriginatingSponsor,
                              @Sponsor                 = @Sponsor,                              
                              @SubmittedBy   		= @SubmittedBy,
			      @SelectedHeaderStatus    = NULL,
                              @DomainUserId            = @DomainUserId

     -- check for errors
     SELECT @Error=@@ERROR
     IF @Error != 0 OR @ReturnCode != 0
     BEGIN
          RETURN -1
     END

     -- We have to return some search parameter names so they can display in the headers.
     -- Only if searching on one and only one value
     IF @Institution IS NOT NULL AND CHARINDEX(',',@Institution) = 0
          SELECT    TOP 1 @Institution = Institution
          FROM      @Subcontracts
     ELSE
          SELECT    @Institution = NULL
     
     IF @Department IS NOT NULL AND CHARINDEX(',',@Department) = 0
          SELECT    TOP 1 @Department =Department
          FROM      @Subcontracts
     ELSE
          SELECT    @Department = NULL

     IF @Unit IS NOT NULL AND CHARINDEX(',',@Unit) = 0
          SELECT    TOP 1 @Unit = Unit
          FROM      @Subcontracts
     ELSE
          SELECT    @Unit = NULL

     IF @PrincipalInvestigator IS NOT NULL AND CHARINDEX(',',@PrincipalInvestigator) = 0
          SELECT    TOP 1 @PrincipalInvestigator = PrincipalInvestigator
          FROM      @Subcontracts
     ELSE
          SELECT    @PrincipalInvestigator = NULL
     
     IF @OriginatingSponsor IS NOT NULL AND CHARINDEX(',',@OriginatingSponsor) = 0
          SELECT    TOP 1 @OriginatingSponsor = SecondarySponsor
          FROM      @Subcontracts
     ELSE
          SELECT    @OriginatingSponsor = NULL

     IF @Sponsor IS NOT NULL AND CHARINDEX(',',@Sponsor) = 0
          SELECT    TOP 1 @Sponsor = SponsorName
          FROM      @Subcontracts
     ELSE
          SELECT    @Sponsor = NULL

     -- Return Result Set
     SELECT    @DeliverableStatus As DeliverableStatus,
			   @Institution AS Institution,
               @Department AS Department,
               @Unit AS Unit,
               @PrincipalInvestigator AS PrincipalInvestigator,
               @OriginatingSponsor AS OriginatingSponsor,
               @Sponsor AS Sponsor,
               GroupBy,
               GroupById1,
               GroupByName1,
               GroupById2,
               GroupByName2,
               GroupByName3,
               SUM(CASE WHEN REPORTSTAT_CODE IN ('REPORTSTAT_61') 
                    THEN DeliverableCount
                    ELSE NULL
               END) AS Pending,
               SUM(CASE WHEN REPORTSTAT_CODE IN ('REPORTSTAT_36') 
                    THEN DeliverableCount
                    ELSE NULL
               END) AS FSRDue,
               SUM(CASE WHEN REPORTSTAT_CODE IN ('REPORTSTAT_37') 
                    THEN DeliverableCount
                    ELSE NULL
               END) AS FSRAssigned,
               SUM(CASE WHEN REPORTSTAT_CODE IN ('REPORTSTAT_38') 
                    THEN DeliverableCount
                    ELSE NULL
               END) AS FSROutToDept,
               SUM(CASE WHEN REPORTSTAT_CODE IN ('REPORTSTAT_39') 
                    THEN DeliverableCount
                    ELSE NULL
               END) AS FSRCompleted,
               SUM(CASE WHEN REPORTSTAT_CODE NOT IN (  'REPORTSTAT_36','REPORTSTAT_37','REPORTSTAT_38',
							'REPORTSTAT_39','REPORTSTAT_61') 
                    THEN DeliverableCount
                    ELSE NULL
               END) AS AllOtherUnCategorized
     FROM      (
               SELECT    @GroupBy AS GroupBy,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN ISNULL(InstitutionId,-1)     -- -1 means not set
                              ELSE NULL
                         END AS GroupById1,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN ISNULL(Institution,'UNASSIGNED')
                              ELSE NULL
                         END AS GroupByName1,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN ISNULL(DepartmentId,-1)  -- -1 means not set
                              ELSE -1
                         END AS GroupById2,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN ISNULL(ChiefCode + ' - ' + Department,'UNASSIGNED')
                              WHEN 'SS' THEN ISNULL(SubmittedBy,'UNASSIGNED')                              
                         END AS GroupByName2,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN ISNULL(ChiefCode,'0')    -- -1 means not set
                              ELSE '0'
                         END AS GroupByName3,
                         REPORTSTAT AS DeliverableStatus,
                         REPORTSTAT_CODE,
                         COUNT(ReportId) AS DeliverableCount
               FROM      @Subcontracts
               GROUP BY  CASE @GroupBy 
                              WHEN 'ORG' THEN ISNULL(InstitutionId,-1)     
                              ELSE NULL
                         END,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN ISNULL(Institution,'UNASSIGNED')
                              ELSE NULL
                         END,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN ISNULL(DepartmentId,-1)  -- -1 means not set
                              ELSE -1
                         END,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN ISNULL(ChiefCode + ' - ' + Department,'UNASSIGNED')
                              WHEN 'SS' THEN ISNULL(SubmittedBy,'UNASSIGNED')                              	
                         END,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN ISNULL(ChiefCode,'0')    -- -1 means not set
                              ELSE '0'
                         END,
                         REPORTSTAT,
                         REPORTSTAT_CODE ) DC
     GROUP BY 
	       GroupBy,
               GroupById1,
               GroupByName1,
               GroupById2,
               GroupByName2,
               GroupByName3
     ORDER BY  GroupBy,
               GroupByName1,
               GroupByName2
               
     -- check for errors
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error != 0 
     BEGIN
          RETURN -1
     END    
     
     -- Return Success
     RETURN 0
END
go

GRANT EXECUTE ON dbo.RptFinancialActivityReport TO InsightReport,Insight
go

